{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Configuring pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# import numpy and pandas\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# used for dates\n",
    "import datetime\n",
    "from datetime import datetime, date\n",
    "\n",
    "# Set some pandas options controlling output format\n",
    "pd.set_option('display.notebook_repr_html', False)\n",
    "pd.set_option('display.max_columns', 8)\n",
    "pd.set_option('display.max_rows', 10)\n",
    "pd.set_option('display.width', 90)\n",
    "\n",
    "# bring in matplotlib for graphics\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date,Open,High,Low,Close,Volume\r",
      "\r\n",
      "7/21/2014,83.46,83.53,81.81,81.93,2359300\r",
      "\r\n",
      "7/18/2014,83.3,83.4,82.52,83.35,4020800\r",
      "\r\n",
      "7/17/2014,84.35,84.63,83.33,83.63,1974000\r",
      "\r\n",
      "7/16/2014,83.77,84.91,83.66,84.91,1755600\r",
      "\r\n"
     ]
    }
   ],
   "source": [
    "# view the first five lines of data/msft.csv\n",
    "!head -n 5 data/msft.csv # mac or Linux\n",
    "# type data/msft.csv # on windows, but shows the entire file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading a CSV into a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "2  7/17/2014  84.35  84.63  83.33  83.63  1974000\n",
       "3  7/16/2014  83.77  84.91  83.66  84.91  1755600\n",
       "4  7/15/2014  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read in msft.csv into a DataFrame\n",
    "msft = pd.read_csv(\"data/msft.csv\")\n",
    "msft[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Specifying the index column when reading a CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "            Open   High    Low  Close   Volume\n",
       "Date                                          \n",
       "7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "7/17/2014  84.35  84.63  83.33  83.63  1974000\n",
       "7/16/2014  83.77  84.91  83.66  84.91  1755600\n",
       "7/15/2014  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use column 0 as the index\n",
    "msft = pd.read_csv(\"data/msft.csv\", index_col=0)\n",
    "msft[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data type inference and specification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Open      float64\n",
       "High      float64\n",
       "Low       float64\n",
       "Close     float64\n",
       "Volume      int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine the types of the columns in this DataFrame\n",
    "msft.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date       object\n",
       "Open      float64\n",
       "High      float64\n",
       "Low       float64\n",
       "Close     float64\n",
       "Volume    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# specify that the Volume column should be a float64\n",
    "msft = pd.read_csv(\"data/msft.csv\", \n",
    "                   dtype = { 'Volume' : np.float64})\n",
    "msft.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Specifying column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        date   open   high    low  close   volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "2  7/17/2014  84.35  84.63  83.33  83.63  1974000\n",
       "3  7/16/2014  83.77  84.91  83.66  84.91  1755600\n",
       "4  7/15/2014  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# specify a new set of names for the columns\n",
    "# all lower case, remove space in Adj Close\n",
    "# also, header=0 skips the header row\n",
    "df = pd.read_csv(\"data/msft.csv\", \n",
    "                 header=0,\n",
    "                 names=['date', 'open', 'high', 'low', \n",
    "                        'close', 'volume'])\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Specifying specific columns to load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "           Close\n",
       "Date            \n",
       "7/21/2014  81.93\n",
       "7/18/2014  83.35\n",
       "7/17/2014  83.63\n",
       "7/16/2014  84.91\n",
       "7/15/2014  83.58"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read in data only in the Date and Close columns\n",
    "# and index by the Date column\n",
    "df2 = pd.read_csv(\"data/msft.csv\", \n",
    "                  usecols=['Date', 'Close'], \n",
    "                  index_col=['Date'])\n",
    "df2[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Saving a DataFrame to a CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# save df2 to a new csv file\n",
    "# also specify naming the index as date\n",
    "df2.to_csv(\"data/msft_modified.csv\", index_label='date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date,Close\r\n",
      "7/21/2014,81.93\r\n",
      "7/18/2014,83.35\r\n",
      "7/17/2014,83.63\r\n",
      "7/16/2014,84.91\r\n"
     ]
    }
   ],
   "source": [
    "# view the start of the file just saved\n",
    "!head -n 5 data/msft_modified.csv\n",
    "#type data/msft_modified.csv # windows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# General field-delimited data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "2  7/17/2014  84.35  84.63  83.33  83.63  1974000\n",
       "3  7/16/2014  83.77  84.91  83.66  84.91  1755600\n",
       "4  7/15/2014  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use read_table with sep=',' to read a CSV\n",
    "df = pd.read_table(\"data/msft.csv\", sep=',')\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|Date|Open|High|Low|Close|Volume\r\n",
      "0|7/21/2014|83.46|83.53|81.81|81.93|2359300\r\n",
      "1|7/18/2014|83.3|83.4|82.52|83.35|4020800\r\n",
      "2|7/17/2014|84.35|84.63|83.33|83.63|1974000\r\n",
      "3|7/16/2014|83.77|84.91|83.66|84.91|1755600\r\n"
     ]
    }
   ],
   "source": [
    "# save as pipe delimited\n",
    "df.to_csv(\"data/msft_piped.txt\", sep='|')\n",
    "# check that it worked\n",
    "!head -n 5 data/msft_piped.txt # osx or Linux\n",
    "# type data/psft_piped.txt # on windows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handling variants of formats in field-delimited data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This is fun because the data does not start on the first line,,,,,\r",
      "\r\n",
      "Date,Open,High,Low,Close,Volume\r",
      "\r\n",
      ",,,,,\r",
      "\r\n",
      "And there is space between the header row and data,,,,,\r",
      "\r\n",
      "7/21/2014,83.46,83.53,81.81,81.93,2359300\r",
      "\r\n",
      "7/18/2014,83.3,83.4,82.52,83.35,4020800\r",
      "\r\n"
     ]
    }
   ],
   "source": [
    "# messy file\n",
    "!head -n 6 data/msft2.csv # osx or Linux\n",
    "# type data/msft2.csv # windows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "2  7/17/2014  84.35  84.63  83.33  83.63  1974000\n",
       "3  7/16/2014  83.77  84.91  83.66  84.91  1755600\n",
       "4  7/15/2014  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read, but skip rows 0, 2 and 3\n",
    "df = pd.read_csv(\"data/msft2.csv\", skiprows=[0, 2, 3])\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date,Open,High,Low,Close,Volume\r",
      "\r\n",
      "7/21/2014,83.46,83.53,81.81,81.93,2359300\r",
      "\r\n",
      "7/18/2014,83.3,83.4,82.52,83.35,4020800\r",
      "\r\n",
      "\r",
      "\r\n",
      "Uh oh, there is stuff at the end.\r",
      "\r\n"
     ]
    }
   ],
   "source": [
    "# another messy file, with the mess at the end\n",
    "!cat data/msft_with_footer.csv # osx or Linux\n",
    "# type data/msft_with_footer.csv # windows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# skip only two lines at the end\n",
    "df = pd.read_csv(\"data/msft_with_footer.csv\", \n",
    "                 skipfooter=2,\n",
    "                 engine = 'python')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "2  7/17/2014  84.35  84.63  83.33  83.63  1974000"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# only process the first three rows\n",
    "pd.read_csv(\"data/msft.csv\", nrows=3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        date   open   high    low  close      vol\n",
       "0   3/3/2014  80.35  81.31  79.91  79.97  5004100\n",
       "1  2/28/2014  82.40  83.42  82.17  83.42  2853200\n",
       "2  2/27/2014  84.06  84.63  81.63  82.00  3676800\n",
       "3  2/26/2014  82.92  84.03  82.43  83.81  2623600\n",
       "4  2/25/2014  83.80  83.80  81.72  83.08  3579100"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# skip 100 lines, then only process the next five\n",
    "pd.read_csv(\"data/msft.csv\", skiprows=100, nrows=5, \n",
    "            header=0,\n",
    "            names=['date', 'open', 'high', 'low', \n",
    "                   'close', 'vol']) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading and writing data in Excel format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0 2014-07-21  83.46  83.53  81.81  81.93  2359300\n",
       "1 2014-07-18  83.30  83.40  82.52  83.35  4020800\n",
       "2 2014-07-17  84.35  84.63  83.33  83.63  1974000\n",
       "3 2014-07-16  83.77  84.91  83.66  84.91  1755600\n",
       "4 2014-07-15  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read excel file\n",
    "# only reads first sheet (msft in this case)\n",
    "df = pd.read_excel(\"data/stocks.xlsx\")\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close    Volume\n",
       "0 2014-07-21  94.99  95.00  93.72  93.94  38887700\n",
       "1 2014-07-18  93.62  94.74  93.02  94.43  49898600\n",
       "2 2014-07-17  95.03  95.28  92.57  93.09  57152000\n",
       "3 2014-07-16  96.97  97.10  94.74  94.78  53396300\n",
       "4 2014-07-15  96.80  96.85  95.03  95.32  45477900"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read from the aapl worksheet\n",
    "aapl = pd.read_excel(\"data/stocks.xlsx\", sheetname='aapl')\n",
    "aapl[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# save to an .XLS file, in worksheet 'Sheet1'\n",
    "df.to_excel(\"data/stocks2.xls\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# write making the worksheet name MSFT\n",
    "df.to_excel(\"data/stocks_msft.xls\", sheet_name='MSFT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# write multiple sheets\n",
    "# requires use of the ExcelWriter class\n",
    "from pandas import ExcelWriter\n",
    "with ExcelWriter(\"data/all_stocks.xls\") as writer:\n",
    "    aapl.to_excel(writer, sheet_name='AAPL')\n",
    "    df.to_excel(writer, sheet_name='MSFT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# write to xlsx\n",
    "df.to_excel(\"data/msft2.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading and writing JSON files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"Date\":{\"0\":1405900800000,\"1\":1405641600000,\"2\":1405555200000,\"3\":1405468800000,\"4\":1405382400000},\"Open\":{\"0\":83.46,\"1\":83.3,\"2\":84.35,\"3\":83.77,\"4\":84.3},\"High\":{\"0\":83.53,\"1\":83.4,\"2\":84.63,\"3\":84.91,\"4\":84.38},\"Low\":{\"0\":81.81,\"1\":82.52,\"2\":83.33,\"3\":83.66,\"4\":83.2},\"Close\":{\"0\":81.93,\"1\":83.35,\"2\":83.63,\"3\":84.91,\"4\":83.58},\"Volume\":{\"0\":2359300,\"1\":4020800,\"2\":1974000,\"3\":1755600,\"4\":1874700}}"
     ]
    }
   ],
   "source": [
    "# wirite the excel data to a JSON file\n",
    "df[:5].to_json(\"data/stocks.json\")\n",
    "!cat data/stocks.json # osx or Linux\n",
    "#type data/stocks.json # windows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   Close       Date   High    Low   Open   Volume\n",
       "0  81.93 2014-07-21  83.53  81.81  83.46  2359300\n",
       "1  83.35 2014-07-18  83.40  82.52  83.30  4020800\n",
       "2  83.63 2014-07-17  84.63  83.33  84.35  1974000\n",
       "3  84.91 2014-07-16  84.91  83.66  83.77  1755600\n",
       "4  83.58 2014-07-15  84.38  83.20  84.30  1874700"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read data in from JSON\n",
    "df_from_json = pd.read_json(\"data/stocks.json\")\n",
    "df_from_json[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# the URL to read\n",
    "url = \"http://www.fdic.gov/bank/individual/failed/banklist.html\"\n",
    "# read it\n",
    "banks = pd.read_html(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                           Bank Name                City\n",
       "0                                Fayette County Bank          Saint Elmo\n",
       "1  Guaranty Bank, (d/b/a BestBank in Georgia & Mi...           Milwaukee\n",
       "2                                     First NBC Bank         New Orleans\n",
       "3                                      Proficio Bank  Cottonwood Heights\n",
       "4                      Seaway Bank and Trust Company             Chicago"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine a subset of the first table read\n",
    "banks[0][0:5].iloc[:,0:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<table border=\"1\" class=\"dataframe\">\r\n",
      "  <thead>\r\n",
      "    <tr style=\"text-align: right;\">\r\n",
      "      <th></th>\r\n",
      "      <th>Date</th>\r\n",
      "      <th>Open</th>\r\n",
      "      <th>High</th>\r\n",
      "      <th>Low</th>\r\n",
      "      <th>Close</th>\r\n",
      "      <th>Volume</th>\r\n"
     ]
    }
   ],
   "source": [
    "# read the stock data\n",
    "df = pd.read_excel(\"data/stocks.xlsx\")\n",
    "# write the first two rows to HTML\n",
    "df.head(2).to_html(\"data/stocks.html\")\n",
    "# check the first 28 lines of the output\n",
    "!head -n 10 data/stocks.html # max or Linux\n",
    "# type data/stocks.html # window, but prints the entire file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading and writing HDF5 format files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<class 'pandas.io.pytables.HDFStore'>\n",
       "File path: data/store.h5\n",
       "/df            frame        (shape->[8,3])"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# seed for replication\n",
    "np.random.seed(123456)\n",
    "# create a DataFrame of dates and random numbers in three columns\n",
    "df = pd.DataFrame(np.random.randn(8, 3), \n",
    "                  index=pd.date_range('1/1/2000', periods=8),\n",
    "                  columns=['A', 'B', 'C'])\n",
    "\n",
    "# create HDF5 store\n",
    "store = pd.HDFStore('data/store.h5')\n",
    "store['df'] = df # persisting happened here\n",
    "store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                   A         B         C\n",
       "2000-01-01  0.469112 -0.282863 -1.509059\n",
       "2000-01-02 -1.135632  1.212112 -0.173215\n",
       "2000-01-03  0.119209 -1.044236 -0.861849\n",
       "2000-01-04 -2.104569 -0.494929  1.071804\n",
       "2000-01-05  0.721555 -0.706771 -1.039575"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read in data from HDF5\n",
    "store = pd.HDFStore(\"data/store.h5\")\n",
    "df = store['df']\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                   A         B         C\n",
       "2000-01-01  1.000000 -0.282863 -1.509059\n",
       "2000-01-02 -1.135632  1.212112 -0.173215\n",
       "2000-01-03  0.119209 -1.044236 -0.861849\n",
       "2000-01-04 -2.104569 -0.494929  1.071804\n",
       "2000-01-05  0.721555 -0.706771 -1.039575"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this changes the DataFrame, but did not persist\n",
    "df.iloc[0].A = 1 \n",
    "# to persist the change, assign the DataFrame to the \n",
    "# HDF5 store object\n",
    "store['df'] = df\n",
    "# it is now persisted\n",
    "# the following loads the store and \n",
    "# shows the first two rows, demonstrating\n",
    "# the the persisting was done\n",
    "pd.HDFStore(\"data/store.h5\")['df'][:5] # it's now in there"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Accessing data on the web and in the cloud"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close    Volume\n",
       "0  28-Apr-17  68.91  69.14  67.69  68.46  39548818\n",
       "1  27-Apr-17  68.15  68.38  67.58  68.27  34970953\n",
       "2  26-Apr-17  68.08  68.31  67.62  67.83  26190770\n",
       "3  25-Apr-17  67.90  68.04  67.60  67.92  30242730\n",
       "4  24-Apr-17  67.48  67.66  67.10  67.53  29769976"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read csv directly from Yahoo! Finance from a URL\n",
    "msft_hist = pd.read_csv(\n",
    "    \"http://www.google.com/finance/historical?\" +\n",
    "    \"q=NASDAQ:MSFT&startdate=Apr+01%2C+2017&\" +\n",
    "    \"enddate=Apr+30%2C+2017&output=csv\")\n",
    "msft_hist[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading and writing from/to SQL databases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# reference SQLite\n",
    "import sqlite3\n",
    "\n",
    "# read in the stock data from CSV\n",
    "msft = pd.read_csv(\"data/msft.csv\")\n",
    "msft[\"Symbol\"]=\"MSFT\"\n",
    "aapl = pd.read_csv(\"data/aapl.csv\")\n",
    "aapl[\"Symbol\"]=\"AAPL\"\n",
    "\n",
    "# create connection\n",
    "connection = sqlite3.connect(\"data/stocks.sqlite\")\n",
    "# .to_sql() will create SQL to store the DataFrame\n",
    "# in the specified table.  if_exists specifies\n",
    "# what to do if the table already exists\n",
    "msft.to_sql(\"STOCK_DATA\", connection, if_exists=\"replace\")\n",
    "aapl.to_sql(\"STOCK_DATA\", connection, if_exists=\"append\")\n",
    "\n",
    "# commit the SQL and close the connection\n",
    "connection.commit()\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "            Date   Open   High    Low  Close   Volume Symbol\n",
       "index                                                       \n",
       "0      7/21/2014  83.46  83.53  81.81  81.93  2359300   MSFT\n",
       "1      7/18/2014  83.30  83.40  82.52  83.35  4020800   MSFT\n",
       "2      7/17/2014  84.35  84.63  83.33  83.63  1974000   MSFT\n",
       "3      7/16/2014  83.77  84.91  83.66  84.91  1755600   MSFT\n",
       "4      7/15/2014  84.30  84.38  83.20  83.58  1874700   MSFT"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# connect to the database file\n",
    "connection = sqlite3.connect(\"data/stocks.sqlite\")\n",
    "\n",
    "# query all records in STOCK_DATA\n",
    "# returns a DataFrame\n",
    "# inde_col specifies which column to make the DataFrame index\n",
    "stocks = pd.io.sql.read_sql(\"SELECT * FROM STOCK_DATA;\", \n",
    "                             connection, index_col='index')\n",
    "\n",
    "# close the connection\n",
    "connection.close()\n",
    "\n",
    "# report the head of the data retrieved\n",
    "stocks[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "            Date   Open   High    Low  Close    Volume Symbol\n",
       "index                                                        \n",
       "1081   5/21/2010  42.22  42.35  40.99  42.00  33610800   MSFT\n",
       "1097   4/29/2010  46.80  46.95  44.65  45.92  47076200   MSFT\n",
       "1826   6/15/2007  89.80  92.10  89.55  92.04  30656400   MSFT\n",
       "3455   3/16/2001  47.00  47.80  46.10  45.33  40806400   MSFT\n",
       "3712   3/17/2000  49.50  50.00  48.29  50.00  50860500   MSFT"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# open the connection\n",
    "connection = sqlite3.connect(\"data/stocks.sqlite\")\n",
    "# construct the query string\n",
    "query = \"SELECT * FROM STOCK_DATA WHERE \" + \\\n",
    "        \"Volume>29200100 AND Symbol='MSFT';\"\n",
    "# execute and close connection\n",
    "items = pd.io.sql.read_sql(query, connection, index_col='index')\n",
    "connection.close()\n",
    "# report the query result\n",
    "items"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading stock data from Google Finance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# import data reader package\n",
    "import pandas_datareader as pdr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "             Open   High    Low  Close    Volume\n",
       "Date                                            \n",
       "2017-04-03  65.81  65.94  65.19  65.55  20400871\n",
       "2017-04-04  65.39  65.81  65.28  65.73  12997449\n",
       "2017-04-05  66.30  66.35  65.44  65.56  21448594\n",
       "2017-04-06  65.60  66.06  65.48  65.73  18103453\n",
       "2017-04-07  65.85  65.96  65.44  65.68  14108533"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read from google and display the head of the data\n",
    "start = datetime(2017, 4, 1)\n",
    "end = datetime(2017, 4, 30)\n",
    "goog = pdr.data.DataReader(\"MSFT\", 'google', start, end)\n",
    "goog[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retrieving options data from Google Finance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# read options for MSFT\n",
    "options = pdr.data.Options('MSFT', 'google')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[datetime.date(2018, 1, 19)]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "options.expiry_dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                             Last    Bid    Ask\n",
       "Strike Expiry     Type Symbol                                  \n",
       "23.0   2018-01-19 call MSFT180119C00023000  45.34  46.90  48.80\n",
       "                  put  MSFT180119P00023000   0.02   0.01   0.03\n",
       "25.0   2018-01-19 call MSFT180119C00025000  43.25  45.05  46.20\n",
       "                  put  MSFT180119P00025000   0.04   0.02   0.04\n",
       "28.0   2018-01-19 call MSFT180119C00028000  41.55  41.85  42.90"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = options.get_options_data(expiry=options.expiry_dates[0])\n",
    "data.iloc[:5,:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Last   Bid   Ask\n",
       "Strike Expiry     Type Symbol                               \n",
       "30.0   2018-01-19 put  MSFT180119P00030000  0.06  0.04  0.07"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get all puts at strike price of $30 (first four columns only)\n",
    "data.loc[(30, slice(None), 'put'), :].iloc[0:5, 0:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Last   Bid   Ask\n",
       "Strike Expiry     Type Symbol                               \n",
       "30.0   2018-01-19 put  MSFT180119P00030000  0.06  0.04  0.07"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# put options at strike of $80, between 2017-06-01 and 2017-06-30\n",
    "data.loc[(30, slice('20180119','20180130'), 'put'), :] \\\n",
    "    .iloc[:, 0:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading economic data from the Federal Reserve Bank of St. Louis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/michaelheydt/anaconda/lib/python3.6/site-packages/ipykernel/__main__.py:5: DeprecationWarning: pandas.core.common.is_list_like is deprecated. import from the public API: pandas.api.types.is_list_like instead\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "                GDP\n",
       "DATE               \n",
       "2012-01-01  15973.9\n",
       "2012-04-01  16121.9\n",
       "2012-07-01  16227.9\n",
       "2012-10-01  16297.3\n",
       "2013-01-01  16475.4"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read GDP data from FRED\n",
    "gdp = pdr.data.FredReader(\"GDP\",\n",
    "                     date(2012, 1, 1), \n",
    "                     date(2014, 1, 27))\n",
    "gdp.read()[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/michaelheydt/anaconda/lib/python3.6/site-packages/ipykernel/__main__.py:4: DeprecationWarning: pandas.core.common.is_list_like is deprecated. import from the public API: pandas.api.types.is_list_like instead\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "            A576RC1A027NBEA\n",
       "DATE                       \n",
       "1929-01-01             50.5\n",
       "1930-01-01             46.2\n",
       "1931-01-01             39.2\n",
       "1932-01-01             30.5\n",
       "1933-01-01             29.0"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get Compensation of employees: Wages and salaries\n",
    "pdr.data.FredReader(\"A576RC1A027NBEA\",\n",
    "                date(1929, 1, 1),\n",
    "                date(2013, 1, 1)).read()[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Accessing Kenneth French data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "         Mkt-RF   SMB   HML   WML    RF\n",
       "Date                                   \n",
       "2010-01   -3.70  2.70 -0.29 -2.23  0.00\n",
       "2010-02    1.24  0.14  0.10  1.59  0.00\n",
       "2010-03    6.30 -0.26  3.18  4.26  0.01\n",
       "2010-04    0.44  3.78  0.77  1.60  0.01\n",
       "2010-05   -9.52  0.17 -2.54 -0.56  0.01"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read from Kenneth French fama global factors data set\n",
    "factors = pdr.data.FamaFrenchReader(\"Global_Factors\").read()\n",
    "factors[0][:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading from the World Bank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                     id                                     name\n",
       "0    1.0.HCount.1.90usd          Poverty Headcount ($1.90 a day)\n",
       "1     1.0.HCount.2.5usd          Poverty Headcount ($2.50 a day)\n",
       "2  1.0.HCount.Mid10to50    Middle Class ($10-50 a day) Headcount\n",
       "3       1.0.HCount.Ofcl  Official Moderate Poverty Rate-National\n",
       "4   1.0.HCount.Poor4uds             Poverty Headcount ($4 a day)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get all indicators\n",
    "from pandas_datareader import wb\n",
    "all_indicators = pdr.wb.get_indicators()\n",
    "all_indicators.iloc[:5,:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                     id                                               name\n",
       "8413        SE.SCH.LIFE  School life expectancy, primary to tertiary, b...\n",
       "9626  SP.DYN.LE00.FE.IN           Life expectancy at birth, female (years)\n",
       "9627     SP.DYN.LE00.IN            Life expectancy at birth, total (years)\n",
       "9628  SP.DYN.LE00.MA.IN             Life expectancy at birth, male (years)\n",
       "9629  SP.DYN.LE60.FE.IN                  Life expectancy at age 60, female"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# search of life expectancy indicators\n",
    "le_indicators = pdr.wb.search(\"life expectancy\")\n",
    "# report first three rows, first two columns\n",
    "le_indicators.iloc[:5,:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          name       capitalCity iso2c\n",
       "0        Aruba        Oranjestad    AW\n",
       "1  Afghanistan             Kabul    AF\n",
       "2       Africa                      A9\n",
       "3       Angola            Luanda    AO\n",
       "4      Albania            Tirane    AL\n",
       "5      Andorra  Andorra la Vella    AD"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get countries and show the 3 digit code and name\n",
    "countries = pdr.wb.get_countries()\n",
    "# show a subset of the country data\n",
    "countries.loc[0:5,['name', 'capitalCity', 'iso2c']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                    SP.DYN.LE00.IN\n",
       "country       year                \n",
       "Canada        2014       81.956610\n",
       "              2013       81.765049\n",
       "              2012       81.562439\n",
       "              2011       81.448780\n",
       "              2010       81.197561\n",
       "...                            ...\n",
       "United States 1984       74.563415\n",
       "              1983       74.463415\n",
       "              1982       74.360976\n",
       "              1981       74.009756\n",
       "              1980       73.609756\n",
       "\n",
       "[105 rows x 1 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get life expectancy at birth for all countries from 1980 to 2014\n",
    "le_data_all = pdr.wb.download(indicator=\"SP.DYN.LE00.IN\", \n",
    "                          start='1980', \n",
    "                          end='2014')\n",
    "le_data_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Canada', 'Mexico', 'United States'], dtype='object', name='country')"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# only US, CAN, and MEX are returned by default\n",
    "le_data_all.index.levels[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/michaelheydt/anaconda/lib/python3.6/site-packages/pandas_datareader/wb.py:145: UserWarning: Non-standard ISO country codes: 1A, 1W, 4E, 6D, 6F, 6L, 6N, 6X, 7E, 8S, A4, A5, A9, B1, B2, B3, B4, B6, B7, B8, C4, C5, C6, C7, C8, C9, D2, D3, D4, D5, D6, D7, D8, D9, EU, F1, F6, JG, L4, L5, L6, L7, M1, M2, N6, O6, OE, R6, S1, S2, S3, S4, T2, T3, T4, T5, T6, T7, V1, V2, V3, V4, XC, XD, XE, XF, XG, XH, XI, XJ, XK, XL, XM, XN, XO, XP, XQ, XT, XU, XY, Z4, Z7, ZB, ZF, ZG, ZJ, ZQ, ZT\n",
      "  'country codes: %s' % tmp, UserWarning)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "               SP.DYN.LE00.IN\n",
       "country  year                \n",
       "Aruba    2012       75.205756\n",
       "         2011       75.081390\n",
       "         2010       74.953537\n",
       "         2009       74.818146\n",
       "         2008       74.675732\n",
       "...                       ...\n",
       "Zimbabwe 1984       61.583951\n",
       "         1983       61.148171\n",
       "         1982       60.605512\n",
       "         1981       60.004829\n",
       "         1980       59.388024\n",
       "\n",
       "[8679 rows x 1 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# retrieve life expectancy at birth for all countries \n",
    "# from 1980 to 2014\n",
    "le_data_all = wb.download(indicator=\"SP.DYN.LE00.IN\", \n",
    "                          country = countries['iso2c'],\n",
    "                          start='1980', \n",
    "                          end='2012')\n",
    "le_data_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "               SP.DYN.LE00.IN                      \n",
       "year                     1980       1981       1982\n",
       "country                                            \n",
       "Afghanistan         41.867537  42.526927  43.230732\n",
       "Albania             70.235976  70.454463  70.685122\n",
       "Algeria             58.164024  59.486756  60.786341\n",
       "American Samoa            NaN        NaN        NaN\n",
       "Andorra                   NaN        NaN        NaN"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#le_data_all.pivot(index='country', columns='year')\n",
    "le_data = le_data_all.reset_index().pivot(index='country', \n",
    "                                          columns='year')\n",
    "# examine pivoted data\n",
    "le_data.iloc[:5,0:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                year\n",
       "SP.DYN.LE00.IN  1980       Cambodia\n",
       "                1981       Cambodia\n",
       "                1982    Timor-Leste\n",
       "                1983    South Sudan\n",
       "                1984    South Sudan\n",
       "dtype: object"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ask what is the name of country for each year\n",
    "# with the least life expectancy\n",
    "country_with_least_expectancy = le_data.idxmin(axis=0)\n",
    "country_with_least_expectancy[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                year\n",
       "SP.DYN.LE00.IN  1980    27.738976\n",
       "                1981    33.449927\n",
       "                1982    38.186220\n",
       "                1983    39.666488\n",
       "                1984    39.999537\n",
       "dtype: float64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# and what is the minimum life expectancy for each year\n",
    "expectancy_for_least_country = le_data.min(axis=0)\n",
    "expectancy_for_least_country[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          Country  Expectancy\n",
       "year                         \n",
       "1980     Cambodia   27.738976\n",
       "1981     Cambodia   33.449927\n",
       "1982  Timor-Leste   38.186220\n",
       "1983  South Sudan   39.666488\n",
       "1984  South Sudan   39.999537"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this merges the two frames together and gives us\n",
    "# year, country and expectancy where there minimum exists\n",
    "least = pd.DataFrame(\n",
    "    data = {'Country': country_with_least_expectancy.values,\n",
    "            'Expectancy': expectancy_for_least_country.values},\n",
    "    index = country_with_least_expectancy.index.levels[1])\n",
    "least[:5]"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
